package com.logicbus.dbcp.sql;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.anysoft.rrm.RRData;
import com.anysoft.rrm.RRDataHandlerFactory;
import com.anysoft.stream.Handler;
import com.anysoft.util.BaseException;
import com.anysoft.util.Properties;
import com.anysoft.util.PropertiesConstants;
import com.anysoft.util.Settings;
import com.logicbus.dbcp.util.ColumnIdRowRenderer;
import com.logicbus.dbcp.util.SqlMetrics;

/**
 * DB相关的一些工具
 * 
 * @author duanyy
 * @since 1.6.3.30
 * 
 * @version 1.6.3.33 [20150723 duanyy] <br>
 * - 增加selectAsObjects方法<br>
 * 
 * @version 1.6.9.2 [20170601 duanyy] <br>
 * - 增加简单的ORM框架 <br>
 * 
 * @version 1.6.9.7 [20170802 duanyy] <br>
 * - 增加AutoCommit相关的操作 <br>
 * 
 * @version 1.6.12.11 [20181206 duanyy] <br>
 * - 优化数据库异常的处理 <br>
 * 
 * @version 1.6.12.32 [20190429] <br>
 * - 输出SQL执行指标 <br>
 *
 * @version 1.6.13.5 [20200605 duanyy] <br>
 * - 针对postgres数据库增加定制化的RowRenderer <br>
 */
public class DBTools {
	protected static boolean metricsOutput = true;
	protected static boolean reserveColumnId = false;
	protected static Handler<RRData> metricsHandler = RRDataHandlerFactory.getHandler();
	static{
		metricsOutput = PropertiesConstants.getBoolean(Settings.get(), "dbcp.metricsOutput", metricsOutput);
		reserveColumnId = PropertiesConstants.getBoolean(Settings.get(),"dbcp.postgres",reserveColumnId);
	}

	/**
	 * 获取缺省的RowRenderer
	 * @param sql sql语句
	 * @return RowRenderer
	 */
	public static RowRenderer<String> getDefaultRowRenderer(String sql){
		if (reserveColumnId){
			return new ColumnIdRowRenderer<String>(sql);
		}else{
			return new RowRenderer.Default<String>();
		}
	}

	public static RowRenderer<Object> getDefaultObjectRowRenderer(String sql){
		if (reserveColumnId){
			return new ColumnIdRowRenderer<Object>(sql);
		}else{
			return new RowRenderer.Default<Object>();
		}
	}

	/**
	 * 通过SQL语句查询单个值
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数集
	 * @return 查询出的对象
	 */
	public static Object selectAsObject(Connection conn, String sql,
			Object... args) {
		long start = System.nanoTime();
		boolean error = false;
		Select selector = new Select(conn);
		try {
			selector.execute(sql, args);
			return selector.single();
		}catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Select.close(selector);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}
		}
	}
	
	/**
	 * 通过SQL语句查询单个值 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数集
	 * @return 查询出的对象
	 */
	public static String selectAsString(Connection conn, String sql,
			Object... args) {
		Object obj = selectAsObject(conn, sql, args);
		if (obj != null) {
			return obj.toString();
		}
		return null;
	}
	
	/**
	 * 通过SQL语句查询单个值（Long）
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param dftValue 缺省值
	 * @param args SQL语句的参数集
	 * @return 结果值(Long)
	 */
	public static Long selectAsLong(Connection conn, String sql, long dftValue,Object... args) {
		String s = selectAsString(conn, sql, args);
		long value = dftValue;
		if (isNotNull(s)) {
			try {
				value = Long.valueOf(s);
			}catch (NumberFormatException ex){
			}
		}
		return value;
	}

	/**
	 * 通过SQL语句查询单个值（Integer）
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param dftValue 缺省值
	 * @param args SQL语句的参数集
	 * @return 结果值(Integer)
	 */	
	public static Integer selectAsInt(Connection conn, String sql, int dftValue,Object... args) {
		String s = selectAsString(conn, sql, args);
		int value = dftValue;
		if (isNotNull(s)) {
			try {
				value = Integer.valueOf(s);
			}catch (NumberFormatException ex){
			}
		}
		return value;
	}
	
	/**
	 * 执行Update语句
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数
	 * @return 影响记录行数
	 */
	public static int update(Connection conn, String sql, Object... args) {
		long start = System.nanoTime();
		boolean error = false;
		Update update = new Update(conn);
		try {
			return update.execute(sql, args);
		}catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Update.close(update);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}
		}
	}

	/**
	 * 执行Insert语句
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数
	 * @return 影响记录行数
	 */
	public static int insert(Connection conn, String sql, Object... args) {
		return update(conn, sql, args);
	}

	/**
	 * 执行Delete语句
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数
	 * @return 影响记录行数
	 */
	public static int delete(Connection conn, String sql, Object... args) {
		return update(conn, sql, args);
	}
	
	/**
	 * 批量执行DML语句
	 * @param conn 数据库连接
	 * @param sqls SQL语句连接
	 * @return 每个SQL执行的状态
	 */
	public static int [] batch(Connection conn,String [] sqls) {
		long start = System.nanoTime();
		boolean error = false;
		Update update = new Update(conn);
		try {
			return update.executeBatch(sqls);
		} catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Update.close(update);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sqls[0]);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}
		}
	}	
	
	/**
	 * Select单行数据
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数
	 * @return 单行数据
	 */
	public static Map<String, String> select(Connection conn, String sql,
			Object... args) {
		long start = System.nanoTime();
		boolean error = false;
		Select selector = new Select(conn);
		try {
			selector.execute(sql, args);
			return selector.singleRowAsString(getDefaultRowRenderer(sql),null);
		} catch (Exception ex){
			error = true;
			throw ex;
		}finally {
			Select.close(selector);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}
		}
	}
	
	/**
	 * Select单行数据
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数
	 * @return 单行数据
	 */
	public static Map<String, Object> selectAsObjects(Connection conn, String sql,
			Object... args) {
		long start = System.nanoTime();
		boolean error = false;		
		Select selector = new Select(conn);
		try {
			selector.execute(sql, args);
			return selector.singleRow(getDefaultObjectRowRenderer(sql),null);
		}catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Select.close(selector);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}
		}
	}	

	/**
	 * Select单行数据
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param renderer 行数据渲染器
	 * @param args SQL语句的参数
	 * @return 单行数据
	 */	
	public static Map<String, String> select(Connection conn, String sql,
			RowRenderer<String> renderer, Object... args) {
		long start = System.nanoTime();
		boolean error = false;		
		Select selector = new Select(conn);
		try {
			selector.execute(sql, args);
			if (renderer != null) {
				return selector.singleRowAsString(renderer, null);
			}
			return selector.singleRowAsString(getDefaultRowRenderer(sql),null);
		}catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Select.close(selector);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}
		}
	}
	
	/**
	 * Select单行数据
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param renderer 行数据渲染器
	 * @param args SQL语句的参数
	 * @return 单行数据
	 */	
	public static Map<String, Object> selectAsObjects(Connection conn, String sql,
			RowRenderer<Object> renderer, Object... args) {
		long start = System.nanoTime();
		boolean error = false;	
		Select selector = new Select(conn);
		try {
			selector.execute(sql, args);
			if (renderer != null) {
				return selector.singleRow(renderer, null);
			}
			return selector.singleRow(getDefaultObjectRowRenderer(sql),null);
		}catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Select.close(selector);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}
		}
	}	

	/**
	 * Select多行数据
	 * @param conn 数据库连接
	 * @param result 结果数组
	 * @param adapter 对象映射适配器
	 * @param sql SQL语句
	 * @param args SQL语句的参数
	 */
	public static <T> void list(
			Connection conn,
			List<T> result,
			ObjectMappingAdapter<T> adapter,
			String sql,
			Object...args){
		long start = System.nanoTime();
		boolean error = false;			
		Select selector = new Select(conn);
		try {
			selector.execute(sql, args);
			selector.result(result, adapter);
		}catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Select.close(selector);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}			
		}
	}
	
	/**
	 * Select多行数据
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数
	 * @return 多行数据
	 */	
	public static List<Map<String, Object>> listAsObject(Connection conn,
			String sql, Object... args) {
		return listAsObject(conn, sql, getDefaultObjectRowRenderer(sql), args);
	}
	/**
	 * Select多行数据
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param renderer 行数据渲染器
	 * @param args SQL语句的参数
	 * @return 多行数据
	 */	
	public static List<Map<String, Object>> listAsObject(Connection conn,
			String sql, RowRenderer<Object> renderer, Object... args) {
		long start = System.nanoTime();
		boolean error = false;		
		Select selector = new Select(conn);
		try {
			selector.execute(sql, args);
			if (renderer != null) {
				return selector.result(renderer);
			}
			return selector.result(getDefaultObjectRowRenderer(sql));
		}catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Select.close(selector);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}			
		}
	}
	/**
	 * Select多行数据
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param args SQL语句的参数
	 * @return 多行数据
	 */	
	public static List<Map<String, String>> list(Connection conn, String sql,
			Object... args) {
		return list(conn, sql, getDefaultRowRenderer(sql), args);
	}
	
	/**
	 * Select多行数据
	 * 
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param renderer 行数据渲染器
	 * @param args SQL语句的参数
	 * @return 多行数据
	 */	
	public static List<Map<String, String>> list(Connection conn, String sql,
			RowRenderer<String> renderer, Object... args) {
		long start = System.nanoTime();
		boolean error = false;			
		Select selector = new Select(conn);
		try {
			selector.execute(sql, args);
			if (renderer != null) {
				return selector.resultAsString(renderer);
			}
			return selector.resultAsString(getDefaultRowRenderer(sql));
		}catch (Exception ex){
			error = true;
			throw ex;
		} finally {
			Select.close(selector);
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics(sql);
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}				
		}
	}	
	
	private static boolean isNotNull(String value){
		return value != null && value.length() > 0;
	}
	
	/**
	 * 提交事务
	 * @param conn 连接
	 */
	public static void commit(Connection conn){
		long start = System.nanoTime();
		boolean error = false;
		try {
			conn.commit();
		} catch (SQLException ex) {
			error = true;
			throw new BaseException("core.e1300",ex.getMessage());
		}finally{
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics("commit");
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}	
		}
	}
	
	/**
	 * 获取当前数据库连接的AutoCommit属性
	 * @param conn 连接
	 * @return autoCommit
	 */
	public static boolean getAutoCommit(Connection conn){
		try {
			return conn.getAutoCommit();
		} catch (SQLException e){
			return true;
		}
	}
	
	/**
	 * 设置当前数据库连接的AutoCommit属性
	 * @param conn 连接
	 * @param autoCommit 
	 */
	public static void setAutoCommit(Connection conn,boolean autoCommit){
		try {
			conn.setAutoCommit(autoCommit);
		}catch (SQLException e){
			// nothing to do
		}
	}
	
	/**
	 * 回滚事务
	 * @param conn
	 */
	public static void rollback(Connection conn){
		long start = System.nanoTime();
		boolean error = false;
		try {
			conn.rollback();
		} catch (SQLException ex) {
			error = true;
			throw new BaseException("core.e1300",ex.getMessage());
		}finally{
			if (metricsOutput && metricsHandler != null){
				SqlMetrics m = new SqlMetrics("commit");
				m.count(System.nanoTime() - start, error);
				metricsHandler.handle(m, System.currentTimeMillis());
			}	
		}
	}	
	
	/**
	 * 关闭所有句柄
	 * @param autoCloseables 句柄列表
	 */
	public static void close(AutoCloseable... autoCloseables){
		for (AutoCloseable c:autoCloseables){
			if (null != c){
				try{
					c.close();
				}catch (Exception ex){
					
				}
			}
		}
	}	
}
